<!doctype html>
<html lang="zh-CN">
<head>

    <meta charset="utf-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>Oracle基础总结 | Jeremy Sze的博客</title>
    <meta property="og:title" content="Oracle基础总结 - Jeremy Sze的博客">
    <meta property="og:type" content="article">
        
    <meta property="article:published_time" content='2020-10-01T10:30:28&#43;08:00'>
        
        
    <meta property="article:modified_time" content='2020-10-01T10:30:28&#43;08:00'>
        
    <meta name="Keywords" content="java,博客">
    <meta name="description" content="Oracle基础总结">
        
    <meta name="author" content="Jeremy Sze">
    <meta property="og:url" content="https://laoer123.gitee.io/laoer123/post/Oracle%E5%9F%BA%E7%A1%80%E6%80%BB%E7%BB%93/">
    <link rel="shortcut icon" href="/favicon.ico" type="image/x-icon">

    <link rel="stylesheet" href='/laoer123/css/normalize.css'>
    <link rel="stylesheet" href='/laoer123/css/style.css'>
    <script type="text/javascript" src="//cdnjs.cloudflare.com/ajax/libs/jquery/3.4.1/jquery.min.js"></script>

    
    <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script>
    <script>
    (adsbygoogle = window.adsbygoogle || []).push({
        google_ad_client: "ca-pub-4031353640611810",
        enable_page_level_ads: true
    });
    </script>
    


    
    
        <link rel="stylesheet" href='/laoer123/css/douban.css'>
    
        <link rel="stylesheet" href='/laoer123/css/other.css'>
    
</head>


<body>
    <header id="header" class="clearfix">
    <div class="container">
        <div class="col-group">
            <div class="site-name ">
                
                    <a id="logo" href="https://laoer123.gitee.io/laoer123">
                        Jeremy Sze的博客
                    </a>
                
                <p class="description">专注于Java、JavaScript、软件开发、web开发、分布式，微服务系统开发</p>
            </div>
            <div>
                <nav id="nav-menu" class="clearfix">
                    <a class="current" href="https://laoer123.gitee.io/laoer123">首页</a>
                    
                    <a  href="https://laoer123.gitee.io/laoer123/about/" title="关于">关于</a>
                    
                </nav>
            </div>
        </div>
    </div>
</header>

    <div id="body">
        <div class="container">
            <div class="col-group">

                <div class="col-8" id="main">
                    
<div class="res-cons">
    <style type="text/css">
    .post-toc {
        position: fixed;
        width: 200px;
        margin-left: -210px;
        padding: 5px 10px;
        font-family: Athelas, STHeiti, Microsoft Yahei, serif;
        font-size: 12px;
        border: 1px solid rgba(0, 0, 0, .07);
        border-radius: 5px;
        background-color: rgba(255, 255, 255, 0.98);
        background-clip: padding-box;
        -webkit-box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        box-shadow: 1px 1px 2px rgba(0, 0, 0, .125);
        word-wrap: break-word;
        white-space: nowrap;
        -webkit-box-sizing: border-box;
        box-sizing: border-box;
        z-index: 999;
        cursor: pointer;
        max-height: 70%;
        overflow-y: auto;
        overflow-x: hidden;
    }

    .post-toc .post-toc-title {
        width: 100%;
        margin: 0 auto;
        font-size: 20px;
        font-weight: 400;
        text-transform: uppercase;
        text-align: center;
    }

    .post-toc .post-toc-content {
        font-size: 15px;
    }

    .post-toc .post-toc-content>nav>ul {
        margin: 10px 0;
    }

    .post-toc .post-toc-content ul {
        padding-left: 20px;
        list-style: square;
        margin: 0.5em;
        line-height: 1.8em;
    }

    .post-toc .post-toc-content ul ul {
        padding-left: 15px;
        display: none;
    }

    @media print,
    screen and (max-width:1057px) {
        .post-toc {
            display: none;
        }
    }
</style>
<div class="post-toc" style="position: absolute; top: 188px;">
    <h2 class="post-toc-title">文章目录</h2>
    <div class="post-toc-content">
        <nav id="TableOfContents">
  <ul>
    <li><a href="#oracle介绍">oracle介绍</a></li>
    <li><a href="#oracle体系结构">Oracle体系结构</a>
      <ul>
        <li><a href="#1-全局数据库物理结构">1. 全局数据库(物理结构)</a></li>
        <li><a href="#2数据库实例服务">2.	数据库实例服务</a></li>
        <li><a href="#3用户">3.	用户</a></li>
        <li><a href="#4表空间逻辑结构">4.	表空间(逻辑结构)</a></li>
        <li><a href="#5数据文件dbfora物理结构">5.	数据文件（dbf、ora物理结构）</a></li>
      </ul>
    </li>
    <li><a href="#oracle系统服务介绍">oracle系统服务介绍</a></li>
    <li><a href="#学习oracle的sql命令分类">学习oracle的sql命令分类</a>
      <ul>
        <li><a href="#创建表空间">创建表空间</a></li>
      </ul>
    </li>
    <li><a href="#用户">用户</a>
      <ul>
        <li><a href="#创建用户ddl">创建用户(DDL)</a></li>
        <li><a href="#用户赋权限dcl">用户赋权限(DCL)</a></li>
      </ul>
    </li>
    <li><a href="#oracle数据类型应用">Oracle数据类型[应用]</a></li>
    <li><a href="#修改scott用户密码">修改scott用户密码</a></li>
    <li><a href="#索引介绍">索引介绍</a>
      <ul>
        <li><a href="#数据库查询数据方式">数据库查询数据方式</a></li>
        <li><a href="#索引的数据结构btreeb树">索引的数据结构Btree(B树)</a></li>
        <li><a href="#索引类型和创建索引的语法">索引类型和创建索引的语法</a></li>
      </ul>
    </li>
    <li><a href="#存储过程">存储过程</a>
      <ul>
        <li><a href="#介绍">介绍</a></li>
        <li><a href="#存储过程语法">存储过程语法</a></li>
      </ul>
    </li>
    <li><a href="#存储函数">存储函数</a></li>
    <li><a href="#触发器">触发器</a></li>
    <li><a href="#游标-cursor">游标 Cursor</a>
      <ul>
        <li><a href="#游标语法">游标语法</a></li>
      </ul>
    </li>
  </ul>
</nav>
    </div>
</div>
<script type="text/javascript">
    $(document).ready(function () {
        var postToc = $(".post-toc");
        if (postToc.length) {
            var leftPos = $("#main").offset().left;
            if(leftPos<220){
                postToc.css({"width":leftPos-10,"margin-left":(0-leftPos)})
            }

            var t = postToc.offset().top - 20,
                a = {
                    start: {
                        position: "absolute",
                        top: t
                    },
                    process: {
                        position: "fixed",
                        top: 20
                    },
                };
            $(window).scroll(function () {
                var e = $(window).scrollTop();
                e < t ? postToc.css(a.start) : postToc.css(a.process)
            })
        }
    })
</script>
    <article class="post">
        <header>
            <h1 class="post-title">Oracle基础总结</h1>
        </header>
        <date class="post-meta meta-date">
            2020年10月1日
        </date>
        
        <div class="post-meta">
            <span>|</span>
            
            <span class="meta-category"><a href='https://laoer123.gitee.io/categories/%E6%95%B0%E6%8D%AE%E5%BA%93'>数据库</a></span>
            
        </div>
        
        
        <div class="post-meta">
            <span id="busuanzi_container_page_pv">|<span id="busuanzi_value_page_pv"></span><span>
                    阅读</span></span>
        </div>
        
        
        <div class="post-content">
            <h2 id="oracle介绍">oracle介绍</h2>
<p>  ORACLE 数据库系统是美国ORACLE 公司（甲骨文）提供的$\color{red}{以分布式数据库为核心的一组软件产品}$，<br>
是目前最流行的客户/服务器(CLIENT/SERVER)或 B/S 体系结构的数据库之一。比如<br>
SilverStream 就是基于数据库的一种中间件。ORACLE 数据库是目前世界上使用最为广泛的数据库管理系统，<br>
$\color{green}{作为一个通用的数据库系统，它具有完整的数据管理功能；作为一个关系数据库，它是一个完备关系的产品；}$  $\color{green}{作为分布式数据库它实现了分布式处理功能}$。<br>
但它的所有知识，只要在一种机型上学习了ORACLE 知识，便能在各种类型的机器上使用它。</p>
<h2 id="oracle体系结构">Oracle体系结构</h2>
<p><img src="/img/oracle%E4%BD%93%E7%B3%BB%E7%BB%93%E6%9E%84.png" alt="alt oracle"></p>
<h3 id="1-全局数据库物理结构">1. 全局数据库(物理结构)</h3>
<p>  Oracle数据库是数据的物理存储()。这就包括（数据文件ORA或者DBF、控制文件、联机日志、参数文件）。<br>
其实Oracle数据库的概念和其它数据库不一样，这里的数据库是一个操作系统只有一个库。可以看作是Oracle就只有一个大数据库。</p>
<p>  mysql可以创建多个数据库,oracle一般只需要一个全局数据库(2G,以后所有数据都放在这里),可以创建多个(不建议)全局数据库位置
<img src="/img/oracle%E7%89%A9%E7%90%86%E7%BB%93%E6%9E%84%E6%96%87%E4%BB%B6.png" alt="alt oracle"></p>
<h3 id="2数据库实例服务">2.	数据库实例服务</h3>
<p>  一个Oracle实例（OracleInstance）由一系列的后台进程（Backguound  Processes)和内存结构（Memory  Structures)组成。一个数据库可以有n个实例。<br>
就是如下图这个服务,用于操作控制物理全局数据库的读写.<br>
<img src="/img/oracle%E5%AE%9E%E4%BE%8B.png" alt="alt oracle"></p>
<h3 id="3用户">3.	用户</h3>
<p>  在mysql里面使用不同数据库,就是创建不同的数据库.但是在oracle想达到使用不同数据库怎么做呢?<br>
答:oracle数据的隔离是以用户为单位,在mysql里面创建数据库为一个项目使用,但是以后在oracle里面创建用户为一个项目使用.</p>
<p>  多全局数据库,就会多个是实例服务.(oracle强烈不建议,有兼容性问题,导致整个数据库可能用不了.)<br>
用户是在实例服务下建立的。不同实例可以建相同名字的用户。</p>
<h3 id="4表空间逻辑结构">4.	表空间(逻辑结构)</h3>
<p>  表空间是Oracle对物理数据库上相关数据文件（ORA或者DBF文件）的逻辑映射。<br>
一个数据库在逻辑上被划分成一到若干个表空间，每个表空间包含了在逻辑上相关联的一组结构。<br>
每个数据库至少有一个表空间(称之为system表空间)。<br>
  每个表空间由同一磁盘上的一个或多个文件组成，这些文件叫数据文件(datafile)。一个数据文件只能属于一个表空间。</p>
<h3 id="5数据文件dbfora物理结构">5.	数据文件（dbf、ora物理结构）</h3>
<p>  数据文件是数据库的物理存储单位。数据库的数据是存储在表空间中的，真正是在某一个或者多个数据文件中。<br>
而一个表空间可以由一个或多个数据文件组成，一个数据文件只能属于一个表空间。<br>
一旦数据文件被加入到某个表空间后，就不能删除这个文件，如果要删除某个数据文件，只能删除其所属于的表空间才行。 <br>
注：表的数据，是有用户放入某一个表空间的，而这个表空间会随机把这些表数据放到一个或者多个数据文件中。</p>
<p>由于oracle的数据库不是普通的概念，oracle是有用户和表空间对数据进行管理和存放的。但是表不是有表空间去查询的，而是由用户去查的。<br>
因为不同用户可以在同一个表空间建立同一个名字的表！这里区分就是用户了！<br>
<img src="/img/oracle%E9%80%BB%E8%BE%91%E7%BB%93%E6%9E%84.png" alt="alt oracle"> 
<img src="/img/oracle%E9%80%BB%E8%BE%91%E7%BB%93%E6%9E%841.png" alt="alt oracle"></p>
<h2 id="oracle系统服务介绍">oracle系统服务介绍</h2>
<p><img src="/img/oracle%E6%9C%8D%E5%8A%A1%E4%BB%8B%E7%BB%8D.png" alt="alt oracle"> 
其中有2个服务是必须开启的,其他服务可以关闭<br>
$\color{green}{OracleServiceORCL,数据库实例服务,当前执行sql命令时用于操作磁盘物理数据库文件数据 }$ 
$\color{green}{OracleOraDb10g_home1TNSListener,监听服务,用于监听接收客户端发送的sql命令,并发送给数据库实例服务去执行.}$</p>
<h2 id="学习oracle的sql命令分类">学习oracle的sql命令分类</h2>
<p>DDL,数据定义语言,create创建/drop销毁<br>
DCL,数据库控制语言,grant授权<br>
DML,数据操纵语言,insert/update/delet等sql语句<br>
DQL,数据查询语言,select等查询语句</p>
<h3 id="创建表空间">创建表空间</h3>
<p>表空间是什么？<br>
  是ORACLE数据库的逻辑单元。数据库&mdash;表空间	一个表空间可以与多个数据文件（物理结构）关联。<br>
一个数据库下可以建立多个表空间，一个表空间可以建立多个用户、一个用户下可以建立多个表。</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql">createtablespace website
datafile<span style="color:#d14">&#39;c:\website.dbf&#39;</span>size100m
autoextendonnext10m
</code></pre></td></tr></table>
</div>
</div><p>website为表空间名称<br>
datafile	指定表空间对应的数据文件<br>
size	后定义的是表空间的初始大小<br>
autoextendon	自动增长，当表空间存储都占满时，自动增长<br>
next后指定的是一次自动增长的大小。</p>
<h2 id="用户">用户</h2>
<h3 id="创建用户ddl">创建用户(DDL)</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">createuser</span> website
identifiedby website
defaulttablespace website
</code></pre></td></tr></table>
</div>
</div><p>identifiedby	后边是用户的密码<br>
defaulttablespace后边是表空间名称<br>
oracle数据库与其它数据库产品的区别在于，表和其它的数据库对象都是存储在用户下的。</p>
<h3 id="用户赋权限dcl">用户赋权限(DCL)</h3>
<p>新创建的用户没有任何权限，登陆后会提示
<img src="/img/oracle%E6%B2%A1%E6%9C%89%E6%9D%83%E9%99%90.png" alt="alt oracle">		
Oracle  中已存在三个重要的角色：connect角色，resource角色，dba角色。<br>
CONNECT 角色： &ndash;是授予最终用户的典型权利，最基本的 ALTER SESSION &ndash;修改会话<br>
  CREATE CLUSTER &ndash;建立聚簇<br>
  CREATE DATABASE LINK &ndash;建立数据库链接<br>
  CREATE SEQUENCE &ndash;建立序列<br>
  CREATE SESSION &ndash;建立会话<br>
  CREATE SYNONYM &ndash;建立同义词<br>
  CREATE VIEW &ndash;建立视图<br>
RESOURCE 角色： &ndash;是授予开发人员的<br>
  CREATE CLUSTER &ndash;建立聚簇<br>
  CREATE PROCEDURE &ndash;建立过程   	
  CREATE SEQUENCE &ndash;建立序列<br>
  CREATE TABLE &ndash;建表<br>
  CREATE TRIGGER &ndash;建立触发器<br>
  CREATE TYPE &ndash;建立类型</p>
<p>DBA 角色：拥有全部特权，是系统最高权限，只有 DBA 才可以创建数据库结构，并且系统权限也需要DBA 授出，且DBA 用户可以操作全体用户的任意基表，包括删除</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">4
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--查看用户角色
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">SELECT</span> <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">FROM</span> USER_ROLE_PRIVS;
<span style="color:#998;font-style:italic">--查看当前用户权限：
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> <span style="color:#000;font-weight:bold">*</span> fromsession_privs;
</code></pre></td></tr></table>
</div>
</div><p>授予权限语法</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-txt" data-lang="txt">格式1:grant权限1,权限2to 用户
格式2:grant 角色 to 用户   --推荐使用
</code></pre></td></tr></table>
</div>
</div><p>进入 system 用户下给用户赋予 dba权限，否则无法正常登陆</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">grant</span> dba <span style="color:#000;font-weight:bold">to</span> itheima89
</code></pre></td></tr></table>
</div>
</div><h2 id="oracle数据类型应用">Oracle数据类型[应用]</h2>
<p><img src="/img/oracle%E5%AD%97%E6%AE%B5%E7%B1%BB%E5%9E%8B.png" alt="alt oracle">
<img src="/img/varchar%E4%B8%8Evachar2.png" alt="alt oracle"></p>
<h2 id="修改scott用户密码">修改scott用户密码</h2>
<p><img src="/img/%E4%BF%AE%E6%94%B9scott%E7%94%A8%E6%88%B7%E5%AF%86%E7%A0%81.png" alt="alt oracle"><br>
<img src="/img/%E4%BF%AE%E6%94%B9scott%E7%94%A8%E6%88%B7%E5%AF%86%E7%A0%811.png" alt="alt oracle"><br>
使用scott用户登录
<img src="/img/%E4%BF%AE%E6%94%B9scott%E7%94%A8%E6%88%B7%E5%AF%86%E7%A0%812.png" alt="alt oracle"></p>
<h2 id="索引介绍">索引介绍</h2>
<p>索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低 i/o 次数,从而提高数据访问性能。<br>
$\color{red}{为什么添加了索引之后，会加快查询速度呢？}$<br>
新华字典：我们都知道新华字典里面包含了很多汉字,这些汉字就好比我们数据库表里面的数据,那么我们如何快速查找厚厚字典里面具体的一个汉字在那一页呢?<br>
我们会首先看字典的目录,通过目录里面按照拼音或偏旁部首方式快速查找到一个小的区域,之后再到小区域范围内查找到想要的数据.<br>
那么这里的字典目录就是索引.</p>
<p>索引的好处就是提高你找到数据的速度，但是正是因为你建了索引，就应该有人专门来维护索引，<br>
维护索引是要有时间精力的开销的，也就是说索引是不能乱建的，所以建索引有个原则：如果有一个字段如果不经常查询，<br>
就不要去建索引。</p>
<h3 id="数据库查询数据方式">数据库查询数据方式</h3>
<p>普通查询数据方式:从数据表第一条位置开始查询,进行逐条查询,直至查找到最后一条,将所有符合的数据找到.<br>
索引查询数据方式:需要建立索引数据,数据库对数据建立B树数据结构,以后用户通过索引B树查找数据</p>
<h3 id="索引的数据结构btreeb树">索引的数据结构Btree(B树)</h3>
<p>B树<br>
  即二叉搜索树：<br>
  1. 所有非叶子结点至多拥有两个儿子（Left和Right）；<br>
  2.所有结点存储一个关键字；<br>
  3.非叶子结点的左指针指向小于其关键字的子树，右指针指向大于其关键字的子树；<br>
<img src="/img/btree%E6%A0%91.png" alt="alt oracle"><br>
B树的搜索，从根结点开始，如果查询的关键字与结点的关键字相等，那么就命中；<br>
否则，如果查询关键字比结点关键字小，就进入左儿子；如果比结点关键字大，就进入右儿子；<br>
如果左儿子或右儿子的指针为空，则报告找不到相应的关键字；</p>
<h3 id="索引类型和创建索引的语法">索引类型和创建索引的语法</h3>
<p>单列索引<br>
  单列索引是基于单个列所建立的索引，比如:<br>
  CREATE index 索引名 on 表名(列名)
复合索引<br>
  复合索引是基于两个列或多个列的索引。在同一张表上可以有多个索引
  create index emp_idx1 on emp(ename,job);<br>
范例：给 person 表的name建立索引</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">index</span> pname_index <span style="color:#000;font-weight:bold">on</span> person(name);
</code></pre></td></tr></table>
</div>
</div><p>范例：给 person 表创建一个 name 和 gender 的索引</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">index</span> pname_gender_index <span style="color:#000;font-weight:bold">on</span> person(name, gender);
</code></pre></td></tr></table>
</div>
</div><h2 id="存储过程">存储过程</h2>
<h3 id="介绍">介绍</h3>
<p>  存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来<br>
执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。</p>
<h3 id="存储过程语法">存储过程语法</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">------------------------存储过程------------------------
</span><span style="color:#998;font-style:italic">-- 介绍,就是给一堆pl/sql代码起一个名字,之后调用,就是存储过程
</span><span style="color:#998;font-style:italic">-- 好处:存储过程是一个预编译的,执行调用的不需要编辑了,所以性能最好
</span><span style="color:#998;font-style:italic">-- 存储过程定义语法:
</span><span style="color:#998;font-style:italic">-- in代表输入参数,传递数据给存储过程,存储过程里面使用,如果不写in或out,默认in输入参数
</span><span style="color:#998;font-style:italic">-- out代表输出参数,用于接收存储过程执行最后的结果,存储过程返回的值
</span><span style="color:#998;font-style:italic">-- 注意:存储过程参数的数据类型不可以写长度
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> <span style="color:#000;font-weight:bold">procedure</span> <span style="color:#a61717;background-color:#e3d2d2">存储过程名字</span>(<span style="color:#a61717;background-color:#e3d2d2">变量名字</span>N [<span style="color:#000;font-weight:bold">in</span>]<span style="color:#000;font-weight:bold">/</span>out数据类型)
<span style="color:#000;font-weight:bold">is</span><span style="color:#000;font-weight:bold">|</span><span style="color:#000;font-weight:bold">as</span><span style="color:#998;font-style:italic">--用于声明,类似declare,is|as不能省略
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#a61717;background-color:#e3d2d2">逻辑处理</span>
<span style="color:#000;font-weight:bold">end</span>;
</code></pre></td></tr></table>
</div>
</div><p>范例1:计算指定员工的年薪,在存储过程内部打印出来</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">17
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--案例1:打印指定员工的年薪
</span><span style="color:#998;font-style:italic"></span>createorreplaceprocedure proc_year_sal(v_empno <span style="color:#0086b3">number</span>)
<span style="color:#000;font-weight:bold">is</span><span style="color:#998;font-style:italic">--用于声明,类似declare,is|as不能省略
</span><span style="color:#998;font-style:italic"></span>   v_year_sal <span style="color:#0086b3">number</span>(<span style="color:#099">8</span>,<span style="color:#099">2</span>);
<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">select</span> sal<span style="color:#000;font-weight:bold">*</span><span style="color:#099">12</span><span style="color:#000;font-weight:bold">+</span>nvl(emp.comm,<span style="color:#099">0</span>) <span style="color:#000;font-weight:bold">into</span> v_year_sal  <span style="color:#000;font-weight:bold">from</span> emp <span style="color:#000;font-weight:bold">where</span> emp.empno<span style="color:#000;font-weight:bold">=</span>v_empno;
<span style="color:#998;font-style:italic">--打印
</span><span style="color:#998;font-style:italic"></span>       dbms_output.put_line(<span style="color:#d14">&#39;年薪:&#39;</span><span style="color:#000;font-weight:bold">||</span>v_year_sal);
<span style="color:#000;font-weight:bold">end</span>;

<span style="color:#998;font-style:italic">--调用存储过程
</span><span style="color:#998;font-style:italic">--方式1:
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">call</span> proc_year_sal(<span style="color:#099">7369</span>);
<span style="color:#998;font-style:italic">--方式2:
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">begin</span>
  proc_year_sal(<span style="color:#099">7369</span>);
<span style="color:#000;font-weight:bold">end</span>;
</code></pre></td></tr></table>
</div>
</div><p>范例2:计算指定员工的年薪,使用输出参数存储年薪,调用存储过程后并打印输出参数数据</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--案例2:获取指定员工的年薪,之后再打印出来
</span><span style="color:#998;font-style:italic"></span>createorreplaceprocedure proc_year_sal(v_empno <span style="color:#0086b3">number</span>,v_year_sal outnumber)
<span style="color:#000;font-weight:bold">is</span><span style="color:#998;font-style:italic">--用于声明,类似declare,is|as不能省略
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">select</span> sal<span style="color:#000;font-weight:bold">*</span><span style="color:#099">12</span><span style="color:#000;font-weight:bold">+</span>nvl(emp.comm,<span style="color:#099">0</span>) <span style="color:#000;font-weight:bold">into</span> v_year_sal  <span style="color:#000;font-weight:bold">from</span> emp <span style="color:#000;font-weight:bold">where</span> emp.empno<span style="color:#000;font-weight:bold">=</span>v_empno;
<span style="color:#000;font-weight:bold">end</span>;

<span style="color:#998;font-style:italic">--调用有输出参数的存储过程只能采用方式2,因为需要一个变量接收返回值
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">declare</span>
    v_year_sal <span style="color:#0086b3">number</span>(<span style="color:#099">8</span>,<span style="color:#099">2</span>);   
<span style="color:#000;font-weight:bold">begin</span>
  proc_year_sal(<span style="color:#099">7788</span>,v_year_sal);
<span style="color:#998;font-style:italic">--打印
</span><span style="color:#998;font-style:italic"></span>  dbms_output.put_line(v_year_sal);
<span style="color:#000;font-weight:bold">end</span>;

</code></pre></td></tr></table>
</div>
</div><h2 id="存储函数">存储函数</h2>
<p>create or replace function  函数名(Name in type, Name in type, &hellip;) return  数据类型 is<br>
结果变量  数据类型;<br>
begin<br>
return( 结果变量);<br>
end 函数名;<br>
存储过程和存储函数的区别<br>
一般来讲，过程和函数的区别在于函数可以有一个返回值；而过程没有返回值。<br>
但过程和函数都可以通过 out 指定一个或多个输出参数。我们可以利用out 参数，在过程和函数中实<br>
现返回多个值。<br>
范例：使用存储函数来查询指定员工的年薪</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">16
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">17
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">18
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">19
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">20
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">21
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">22
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">------------------------------存储函数------------------------------------
</span><span style="color:#998;font-style:italic">----------单行函数或多行函数是数据库已有的,存储函数我们要自定义函数
</span><span style="color:#998;font-style:italic"></span>createorreplacefunction函数名字(<span style="color:#a61717;background-color:#e3d2d2">变量名字</span>N [<span style="color:#000;font-weight:bold">in</span>]<span style="color:#000;font-weight:bold">/</span>out数据类型)
return数据类型
<span style="color:#000;font-weight:bold">is</span><span style="color:#000;font-weight:bold">|</span><span style="color:#000;font-weight:bold">as</span><span style="color:#998;font-style:italic">--用于声明,类似declare,is|as不能省略
</span><span style="color:#998;font-style:italic"></span>
<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#a61717;background-color:#e3d2d2">逻辑处理</span>
<span style="color:#000;font-weight:bold">end</span>;

<span style="color:#998;font-style:italic">--案例:获取指定员工的年薪
</span><span style="color:#998;font-style:italic"></span>createorreplacefunction func_year_sal(v_empno <span style="color:#0086b3">number</span>)
returnnumber
<span style="color:#000;font-weight:bold">is</span><span style="color:#998;font-style:italic">--用于声明,类似declare,is|as不能省略
</span><span style="color:#998;font-style:italic"></span>    v_year_sal <span style="color:#0086b3">number</span>(<span style="color:#099">8</span>,<span style="color:#099">2</span>);
<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">select</span> sal<span style="color:#000;font-weight:bold">*</span><span style="color:#099">12</span><span style="color:#000;font-weight:bold">+</span>nvl(emp.comm,<span style="color:#099">0</span>) <span style="color:#000;font-weight:bold">into</span> v_year_sal  <span style="color:#000;font-weight:bold">from</span> emp <span style="color:#000;font-weight:bold">where</span> emp.empno<span style="color:#000;font-weight:bold">=</span>v_empno;
<span style="color:#000;font-weight:bold">return</span> v_year_sal;
<span style="color:#000;font-weight:bold">end</span>;

<span style="color:#998;font-style:italic">--调用函数
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">select</span> empno,ename,sal,func_year_sal(empno) <span style="color:#a61717;background-color:#e3d2d2">年薪</span><span style="color:#000;font-weight:bold">from</span> emp;
</code></pre></td></tr></table>
</div>
</div><p>总结</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">3
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--总结:存储过程与存储函数区别
</span><span style="color:#998;font-style:italic">--     1.存储函数可以直接内部返回,不需要传递输出参数,使用非常方便,经常在sql查询语句内部使用
</span><span style="color:#998;font-style:italic">--     2.一般企业应用,java代码会直接执行sql语句,也会直接执行存储过程.
</span></code></pre></td></tr></table>
</div>
</div><h2 id="触发器">触发器</h2>
<p>数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句<br>
(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。<br>
触发器可用于<br>
  数据确认<br>
  实施复杂的安全性检查<br>
  做审计，跟踪表上所做的数据操作等<br>
  数据的备份和同步</p>
<p>触发器的类型<br>
语句级触发器 ：在指定的操作语句操作之前或之后执行一次，不管这条语句影响了多少行 。
行级触发器（FOR EACH ROW） ：触发语句作用的每一条记录都被触发。在行级触 发器中使用 old 和 new伪记录变量, 识别值的状态。</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--介绍:当执行insert/update/delete等数据表改变的sql语句时就会触发触发器的执行
</span><span style="color:#998;font-style:italic">--语法格式:
</span><span style="color:#998;font-style:italic"></span>createorreplacetrigger触发器名字
<span style="color:#000;font-weight:bold">before</span><span style="color:#000;font-weight:bold">|</span><span style="color:#000;font-weight:bold">after</span><span style="color:#998;font-style:italic">--设置触发器在改变数据之前或之后执行
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">insert</span> <span style="color:#000;font-weight:bold">|</span> <span style="color:#000;font-weight:bold">update</span> <span style="color:#000;font-weight:bold">|</span> <span style="color:#000;font-weight:bold">delete</span> [of列名] <span style="color:#998;font-style:italic">--设置具体改变数据的命令,可以写一个,也可写多个,多个使用or连接
</span><span style="color:#998;font-style:italic"></span>on表名<span style="color:#998;font-style:italic">--改变那个表的时候触发
</span><span style="color:#998;font-style:italic"></span>[foreachrow]    <span style="color:#998;font-style:italic">--触发器的级别
</span><span style="color:#998;font-style:italic"></span>[<span style="color:#000;font-weight:bold">declare</span>]

<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#a61717;background-color:#e3d2d2">逻辑处理</span>
<span style="color:#000;font-weight:bold">end</span>;
</code></pre></td></tr></table>
</div>
</div><p>范例：插入员工后打印一句话“有新入职人员加入”</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--案例1:当插入emp表数据的时候,打印&#34;有新入职人员加入&#34;
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">create</span> <span style="color:#000;font-weight:bold">or</span> <span style="color:#000;font-weight:bold">replace</span> <span style="color:#000;font-weight:bold">trigger</span> trg_emp_insert
<span style="color:#000;font-weight:bold">after</span><span style="color:#998;font-style:italic">--设置插入数据之后触发
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">insert</span><span style="color:#998;font-style:italic">--设置插入语句触发
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">on</span> emp           <span style="color:#998;font-style:italic">--改变emp表的时候触发
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">begin</span>
  dbms_output.put_line(<span style="color:#d14">&#39;有新入职人员加入&#39;</span>);
<span style="color:#000;font-weight:bold">end</span>;
<span style="color:#998;font-style:italic">--手动插入emp数据
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">insert</span> <span style="color:#000;font-weight:bold">into</span> emp(empno,ename,deptno) <span style="color:#000;font-weight:bold">values</span>(<span style="color:#099">1</span>,<span style="color:#d14">&#39;tom&#39;</span>,<span style="color:#099">20</span>);
</code></pre></td></tr></table>
</div>
</div><p>范例：要求当前系统日期为'2018-08-24&rsquo;,不允许插入数据,给出报错</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">15
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#000;font-weight:bold">create</span>  <span style="color:#000;font-weight:bold">or</span>  <span style="color:#000;font-weight:bold">replace</span>  <span style="color:#000;font-weight:bold">trigger</span> trg_emp_insert
<span style="color:#000;font-weight:bold">before</span><span style="color:#998;font-style:italic">--设置触发器在改变数据之前之前执行
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">insert</span><span style="color:#998;font-style:italic">--设置insert触发
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">on</span> emp           <span style="color:#998;font-style:italic">--改变emp表的时候触发
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">if</span> to_char(sysdate,<span style="color:#d14">&#39;yyyy-MM-dd&#39;</span>)<span style="color:#000;font-weight:bold">=</span><span style="color:#d14">&#39;2018-08-24&#39;</span><span style="color:#000;font-weight:bold">then</span>
<span style="color:#998;font-style:italic">--发生错误的函数,传递2个参数
</span><span style="color:#998;font-style:italic">--  参数1:状态码,oracle给开发人员预留了-20001~-29999,开发人员使用任意其中一个
</span><span style="color:#998;font-style:italic">--  参数2:错误信息
</span><span style="color:#998;font-style:italic"></span>    raise_application_error(<span style="color:#000;font-weight:bold">-</span><span style="color:#099">20001</span>,<span style="color:#d14">&#39;非工作日不允许插入数据&#39;</span>);
endif;
<span style="color:#000;font-weight:bold">end</span>;
<span style="color:#998;font-style:italic">--手动插入emp数据
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">insert</span> <span style="color:#000;font-weight:bold">into</span> emp(empno,ename,deptno) <span style="color:#000;font-weight:bold">values</span>(<span style="color:#099">2</span>,<span style="color:#d14">&#39;tom2&#39;</span>,<span style="color:#099">20</span>);

</code></pre></td></tr></table>
</div>
</div><h2 id="游标-cursor">游标 Cursor</h2>
<p>在写 java 程序中有集合的概念，那么在 pl/sql 中也会用到多条记录，这时候我们就要用到游标，游标可以存储查询返回的多条数据。</p>
<h3 id="游标语法">游标语法</h3>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">--介绍,是一个数据集,可以存储多条数据,与java中jdbc里面ResultSet一样.
</span><span style="color:#998;font-style:italic">--声明游标语法:
</span><span style="color:#998;font-style:italic"></span>cursor游标名字is sql查询语句; 
<span style="color:#998;font-style:italic">--使用游标语法:
</span><span style="color:#998;font-style:italic"></span>open游标名称;  <span style="color:#998;font-style:italic">--打开游标
</span><span style="color:#998;font-style:italic"></span>loop<span style="color:#998;font-style:italic">--通过遍历,将游标里面的数据一条一条的获取出来
</span><span style="color:#998;font-style:italic"></span>fetch游标名称into记录型变量;<span style="color:#998;font-style:italic">--获取游标里面一条数据
</span><span style="color:#998;font-style:italic"></span>exitwhen游标名称<span style="color:#000;font-weight:bold">%</span>notfound;   <span style="color:#998;font-style:italic">--游标里面没有数据退出循环
</span><span style="color:#998;font-style:italic"></span><span style="color:#a61717;background-color:#e3d2d2">逻辑处理获取记录型变量里面的数据</span>
endloop;
close游标名称;  <span style="color:#998;font-style:italic">--关闭游标
</span></code></pre></td></tr></table>
</div>
</div><p>游标案例</p>
<div class="highlight"><div style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4">
<table style="border-spacing:0;padding:0;margin:0;border:0;width:auto;overflow:auto;display:block;"><tr><td style="vertical-align:top;padding:0;margin:0;border:0;">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 1
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 2
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 3
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 4
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 5
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 6
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 7
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 8
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f"> 9
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">10
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">11
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">12
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">13
</span><span style="margin-right:0.4em;padding:0 0.4em 0 0.4em;color:#7f7f7f">14
</span></code></pre></td>
<td style="vertical-align:top;padding:0;margin:0;border:0;;width:100%">
<pre style="background-color:#fff;-moz-tab-size:4;-o-tab-size:4;tab-size:4"><code class="language-sql" data-lang="sql"><span style="color:#998;font-style:italic">---示例代码:查找20号部门所有员工,将其姓名,薪水,job打印出来
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">declare</span><span style="color:#998;font-style:italic">--声明
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">cursor</span> cur_emp isselect <span style="color:#000;font-weight:bold">*</span> <span style="color:#000;font-weight:bold">from</span> emp <span style="color:#000;font-weight:bold">where</span> deptno<span style="color:#000;font-weight:bold">=</span><span style="color:#099">20</span>; 
  v_row emp<span style="color:#000;font-weight:bold">%</span>rowtype;  
<span style="color:#000;font-weight:bold">begin</span>
<span style="color:#000;font-weight:bold">open</span> cur_emp;<span style="color:#998;font-style:italic">--开启游标
</span><span style="color:#998;font-style:italic"></span>loop
<span style="color:#000;font-weight:bold">fetch</span> cur_emp <span style="color:#000;font-weight:bold">into</span> v_row;
exitwhen cur_emp<span style="color:#000;font-weight:bold">%</span>notfound;
<span style="color:#998;font-style:italic">--打印
</span><span style="color:#998;font-style:italic"></span>       dbms_output.put_line(v_row.ename<span style="color:#000;font-weight:bold">||</span><span style="color:#d14">&#39;--&#39;</span><span style="color:#000;font-weight:bold">||</span>v_row.sal<span style="color:#000;font-weight:bold">||</span><span style="color:#d14">&#39;--&#39;</span><span style="color:#000;font-weight:bold">||</span>v_row.job);
endloop;
<span style="color:#000;font-weight:bold">close</span> cur_emp;<span style="color:#998;font-style:italic">--关闭游标
</span><span style="color:#998;font-style:italic"></span><span style="color:#000;font-weight:bold">end</span>;
</code></pre></td></tr></table>
</div>
</div>
        </div>

        
<div class="post-archive">
    <ul class="post-copyright">
        <li><strong>原文作者：</strong><a rel="author" href="https://laoer123.gitee.io/laoer123">Jeremy Sze</a></li>
        <li style="word-break:break-all"><strong>原文链接：</strong><a href="https://laoer123.gitee.io/laoer123/post/Oracle%E5%9F%BA%E7%A1%80%E6%80%BB%E7%BB%93/">https://laoer123.gitee.io/laoer123/post/Oracle%E5%9F%BA%E7%A1%80%E6%80%BB%E7%BB%93/</a></li>
        <li><strong>版权声明：</strong>本作品采用<a rel="license" href="https://creativecommons.org/licenses/by-nc-nd/4.0/">知识共享署名-非商业性使用-禁止演绎 4.0 国际许可协议</a>进行许可，非商业转载请注明出处（作者，原文链接），商业转载请联系作者获得授权。</li>
    </ul>
</div>
<br/>



        

<div class="post-archive">
    <h2>See Also</h2>
    <ul class="listing">
        
        <li><a href="/laoer123/post/JdbcTemplate%E6%80%BB%E7%BB%93/">JDBCTemplate总结</a></li>
        
        <li><a href="/laoer123/post/nginx%E5%85%A5%E9%97%A8/">nginx入门</a></li>
        
        <li><a href="/laoer123/post/JWT/">JWT总结</a></li>
        
        <li><a href="/laoer123/post/Rancher_.influxDB_cAdvisor_Grafana/">Rancher_InfluxDB_CAdvisor_Grafana总结</a></li>
        
        <li><a href="/laoer123/post/%E5%89%8D%E5%90%8E%E7%AB%AF%E5%88%86%E7%A6%BB%E4%B9%8B%E5%89%8D%E7%AB%AF%E6%80%BB%E7%BB%93/">前后分离之前端总结</a></li>
        
    </ul>
</div>


        <div class="post-meta meta-tags">
            
            <ul class="clearfix">
                
                <li><a href='https://laoer123.gitee.io/tags/ORACLE'>ORACLE</a></li>
                
            </ul>
            
        </div>
    </article>
    
    <div id="disqus_thread"></div>
<script type="application/javascript">
    var disqus_config = function () {
    
    
    
    };
    (function() {
        if (["localhost", "127.0.0.1"].indexOf(window.location.hostname) != -1) {
            document.getElementById('disqus_thread').innerHTML = 'Disqus comments not available by default when the website is previewed locally.';
            return;
        }
        var d = document, s = d.createElement('script'); s.async = true;
        s.src = '//' + "yourdiscussshortname" + '.disqus.com/embed.js';
        s.setAttribute('data-timestamp', +new Date());
        (d.head || d.body).appendChild(s);
    })();
</script>
<noscript>Please enable JavaScript to view the <a href="https://disqus.com/?ref_noscript">comments powered by Disqus.</a></noscript>
<a href="https://disqus.com" class="dsq-brlink">comments powered by <span class="logo-disqus">Disqus</span></a>

    
    
</div>

                </div>

                <div id="secondary">
    <section class="widget">
        <form id="search" action='https://laoer123.gitee.io/laoer123/search/' method="get" accept-charset="utf-8" target="_blank" _lpchecked="1">
      
      <input type="text" name="q" maxlength="20" placeholder="Search">
      <input type="hidden" name="sitesearch" value="https://laoer123.gitee.io/laoer123">
      <button type="submit" class="submit icon-search"></button>
</form>
    </section>
    
    <section class="widget">
        <h3 class="widget-title">最近文章</h3>
<ul class="widget-list">
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/springcloud%E4%B8%8EspringcloudAlibaba%E6%80%BB%E7%BB%93/" title="springcloud与springcloudAlibaba总结">springcloud与springcloudAlibaba总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Seata%E6%80%BB%E7%BB%93/" title="Seata总结">Seata总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/OpenFegin%E6%80%BB%E7%BB%93/" title="OpenFegin总结">OpenFegin总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/SpringCloudGateWay%E6%80%BB%E7%BB%93/" title="SpringCloudGateWay总结">SpringCloudGateWay总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/SpringCloudRibbon/" title="SpringCloudRibbon总结">SpringCloudRibbon总结</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E9%85%8D%E7%BD%AE%E6%B3%A8%E5%86%8C%E4%B8%8E%E5%8F%91%E7%8E%B0/" title="Nacos配置注册与发现">Nacos配置注册与发现</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E9%85%8D%E7%BD%AE%E7%AE%A1%E7%90%86%E6%BC%94%E7%A4%BA/" title="Nacos配置管理演示">Nacos配置管理演示</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Nacos%E6%80%BB%E7%BB%93/" title="什么是Nacos">什么是Nacos</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Sentinel%E6%96%B0%E6%89%8B%E6%8C%87%E5%8D%97/" title="Sentinel新手指南">Sentinel新手指南</a>
    </li>
    
    <li>
        <a href="https://laoer123.gitee.io/laoer123/post/Sentinel%E6%8E%A7%E5%88%B6%E5%8F%B0/" title="Sentinel控制台">Sentinel控制台</a>
    </li>
    
</ul>
    </section>

    

    <section class="widget">
        <h3 class="widget-title">分类</h3>
<ul class="widget-list">
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/Linux/">Linux (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/NoSql%E6%95%B0%E6%8D%AE%E5%BA%93/">NoSql数据库 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloud/">SpringCloud (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloudAlibaba/">SpringCloudAlibaba (11)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/SpringCloudAlibabaSpringCloud/">SpringCloudAlibaba,SpringCloud (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/docker/">docker (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/java%E5%9F%BA%E7%A1%80/">java基础 (10)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/spring/">spring (4)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E4%BA%8B%E5%8A%A1/">事务 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%89%8D%E7%AB%AF%E5%BE%AE%E6%9C%8D%E5%8A%A1/">前端微服务 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E5%89%8D%E7%AB%AF%E6%A1%86%E6%9E%B6/">前端框架 (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%8C%81%E7%BB%AD%E9%9B%86%E6%88%90/">持续集成 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%90%9C%E7%B4%A2%E5%BC%95%E6%93%8E/">搜索引擎 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%95%B0%E6%8D%AE%E5%B1%82%E6%A1%86%E6%9E%B6/">数据层框架 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%95%B0%E6%8D%AE%E5%BA%93/">数据库 (5)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%9C%8D%E5%8A%A1%E5%99%A8/">服务器 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%9E%B6%E6%9E%84/">架构 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E6%B6%88%E6%81%AF%E4%B8%AD%E9%97%B4%E4%BB%B6/">消息中间件 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E7%89%88%E6%9C%AC%E6%8E%A7%E5%88%B6/">版本控制 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E7%BC%93%E5%AD%98/">缓存 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E8%AE%A4%E8%AF%81/">认证 (2)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E8%BF%90%E7%BB%B4/">运维 (1)</a></li>
    
    <li><a href="https://laoer123.gitee.io/laoer123/categories/%E9%83%A8%E7%BD%B2/">部署 (1)</a></li>
    
</ul>
    </section>

    <section class="widget">
        <h3 class="widget-title">标签</h3>
<div class="tagcloud">
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Dubbo/">Dubbo</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ES6/">ES6</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ElasticSearch/">ElasticSearch</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ElementUI/">ElementUI</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Eureka/">Eureka</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Feign/">Feign</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JDBCTemplate/">JDBCTemplate</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JPA/">JPA</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/JWT/">JWT</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Jenkins/">Jenkins</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Linux%E5%9F%BA%E7%A1%80/">Linux基础</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Linux%E9%85%8D%E7%BD%AE%E8%81%94%E7%BD%91/">Linux配置联网</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/MongoDB/">MongoDB</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Nacos/">Nacos</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/ORACLE/">ORACLE</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/OpenFegin/">OpenFegin</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/RabbitMQ/">RabbitMQ</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Seata/">Seata</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/Sentinel/">Sentinel</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudGateWay/">SpringCloudGateWay</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudRibbon/">SpringCloudRibbon</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/SpringCloudSpringCloudAlibaba/">SpringCloud,SpringCloudAlibaba</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/VUE/">VUE</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/docker/">docker</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/git/">git</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/gogs/">gogs</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/hystrix/">hystrix</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/mysql/">mysql</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/nginx/">nginx</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/nodeJS/">nodeJS</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/npm/">npm</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springCache/">springCache</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springDataJPA/">springDataJPA</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springboot/">springboot</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springcloudbus/">springcloudbus</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springcloudconfig/">springcloudconfig</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/springdataredis/">springdataredis</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/zuul/">zuul</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%86%E5%B8%83%E5%BC%8F/">分布式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%86%E5%B8%83%E5%BC%8F%E4%BA%8B%E5%8A%A1/">分布式事务</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%88%9B%E5%BB%BA%E6%A8%A1%E5%BC%8F/">创建模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E5%BE%AE%E6%9C%8D%E5%8A%A1/">微服务</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E6%8E%92%E5%BA%8F/">排序</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E6%9C%8D%E5%8A%A1%E9%95%9C%E5%83%8F%E7%9A%84%E6%9E%84%E5%BB%BA%E4%B8%8E%E9%83%A8%E7%BD%B2/">服务镜像的构建与部署</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E7%BB%93%E6%9E%84%E6%A8%A1%E5%BC%8F/">结构模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%A1%8C%E4%B8%BA%E6%A8%A1%E5%BC%8F/">行为模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%AE%BE%E8%AE%A1%E6%A8%A1%E5%BC%8F/">设计模式</a>
    
    <a href="https://laoer123.gitee.io/laoer123/tags/%E8%BF%90%E7%BB%B4/">运维</a>
    
</div>
    </section>

    
<section class="widget">
    <h3 class="widget-title">友情链接</h3>
    <ul class="widget-list">
        
        <li>
            <a target="_blank" href="https://www.cnblogs.com/Jeremy95-Sze/" title="Jeremy Sze的博客">Jeremy Sze的博客</a>
        </li>
        
        <li>
            <a target="_blank" href="https://github.com/Jeremy95-Sze" title="Jeremy Sze的github">Jeremy Sze的github</a>
        </li>
        
    </ul>
</section>


    <section class="widget">
        <h3 class="widget-title">其它</h3>
        <ul class="widget-list">
            <li><a href="https://laoer123.gitee.io/laoer123/index.xml">文章 RSS</a></li>
        </ul>
    </section>
</div>
            </div>
        </div>
    </div>
    <footer id="footer">
    <div class="container">
        &copy; 2023 <a href="https://laoer123.gitee.io/laoer123">Jeremy Sze的博客 By Jeremy Sze</a>.
        Powered by <a rel="nofollow noreferer noopener" href="https://gohugo.io" target="_blank">Hugo</a>.
        <a href="https://www.flysnow.org/" target="_blank">Theme</a> based on <a href="https://github.com/flysnow-org/maupassant-hugo" target="_blank">maupassant</a>.
        
    </div>
</footer>


    
    <script src="//cdnjs.cloudflare.com/ajax/libs/webfont/1.6.28/webfontloader.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/snap.svg/0.5.1/snap.svg-min.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/underscore.js/1.9.1/underscore-min.js" crossorigin="anonymous"></script>
        <script src="//cdnjs.cloudflare.com/ajax/libs/js-sequence-diagrams/1.0.6/sequence-diagram-min.js" crossorigin="anonymous"></script>
        <script>(function () {
            if (!window.Diagram) return;
            const blocks = document.querySelectorAll('pre code.language-sequence');
            for (let i = 0; i < blocks.length; i++) {
                const block = blocks[i];
                
                const rootElement = block.parentNode;
                const container = document.createElement('div');
                const id = `js-sequence-diag-${i}`;
                container.id = id;
                container.className = 'align-center';
                container.setAttribute("style", "overFlow-x:auto");
                rootElement.parentNode.replaceChild(container, rootElement);

                const diagram = Diagram.parse(block.childNodes[0].nodeValue);
                diagram.drawSVG(id, window.sequenceDiagramsOptions
                    ? window.sequenceDiagramsOptions
                    : { theme: 'simple' });
            }
        })();
        </script><script type="text/javascript">
        window.MathJax = {
            tex2jax: {
                inlineMath: [['$', '$']],
                processEscapes: true
                }
            };
    </script>
    <script src='https://cdnjs.cloudflare.com/ajax/libs/mathjax/2.7.5/MathJax.js?config=TeX-MML-AM_CHTML' async></script>


<a id="rocket" href="#top"></a>
<script type="text/javascript" src='/laoer123/js/totop.js?v=0.0.0' async=""></script>

<script type="application/javascript">
var doNotTrack = false;
if (!doNotTrack) {
	window.ga=window.ga||function(){(ga.q=ga.q||[]).push(arguments)};ga.l=+new Date;
	ga('create', 'GA ID', 'auto');
	
	ga('send', 'pageview');
}
</script>
<script async src='https://www.google-analytics.com/analytics.js'></script>



    <script type="text/javascript" src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js" async></script>




    <script src='/laoer123/js/douban.js'></script>

</body>

</html>